import mysql.connector
import os
from dotenv import load_dotenv

load_dotenv()

# Database configuration
DB_NAME = os.getenv('DB_NAME')
db_config = {
    'user': os.getenv('DB_USER'),
    'password': os.getenv('DB_PASSWORD'),
    'host': os.getenv('DB_HOST'),
    'database': DB_NAME
}


def update_user_scores():
    try:
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()

        # SQL to calculate user statistics from game_records
        sql = """
        INSERT INTO user_scores (user_id, score, games, spell_count, trans_count, avg_rate)
        SELECT
            gr.user_id,
            SUM(gr.score) as total_score,
            COUNT(*) as total_games,
            SUM(CASE WHEN gr.game_mode = 'spell' THEN 1 ELSE 0 END) as spell_games,
            SUM(CASE WHEN gr.game_mode = 'translate' THEN 1 ELSE 0 END) as trans_games,
            AVG(gr.completion_rate) as average_rate
        FROM game_records gr
        WHERE gr.status = 'completed'
        GROUP BY gr.user_id
        ON DUPLICATE KEY UPDATE
            score = VALUES(score),
            games = VALUES(games),
            spell_count = VALUES(spell_count),
            trans_count = VALUES(trans_count),
            avg_rate = VALUES(avg_rate)
        """

        cursor.execute(sql)
        conn.commit()
        print("User scores updated successfully.")
    except Exception as e:
        print(f"An error occurred: {e}")
    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    print("Starting to update user scores...")
    update_user_scores()
    print("Process completed.")
